<?php
declare(strict_types=1);

namespace App\Service;

use App\Helper\Log;
use App\Helper\Util;
use Hyperf\DbConnection\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use Hyperf\Filesystem\FilesystemFactory;
use Hyperf\Utils\ApplicationContext;

/**
 * Excel服务
 */
class ExcelService 
{
    private $save_path = "/opt/www/app/Assets/excel/";
    /**
     * 检查文件是否存在
     * [checkFile description]
     * @param  [type] $file_name [description]
     * @return [type]            [description]
     */
    public function checkFile($file_name)
    {
        $file_path = $this->save_path.$file_name;
        return file_exists($file_path);
    }
    /**
     * 获取文件数据
     * [getDataSource description]
     * @param  [type] $file_name [description]
     * @return [type]            [description]
     */
    public function getDataSource($file_name){
        $file_path = $this->save_path.$file_name;
        $data = file_get_contents($file_path);
        unlink($file_path);
        return $data;
    }
    /**
     * 导出入口
     * [doExport description]
     * @param  [type] $data   [description]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public function doExport($data,$params)
    {
        // 表头
        // $headers = ['ID', '姓名', '年龄'];
        $A1_text = $params['office_info']['office_info']['name']."排班表（".$params['begin_date']."至".$params['end_date']."）";
        // 文件名
        $save_path = $this->save_path;
        $file_name = date("YmdHis").$params['office_info']['office_info']['id'].".xlsx";
        $file_path = $save_path.$file_name;
        // 标题
        $title = "";

        $date_list = $data[0]['list']??NULL;
        if(empty($date_list)){
            return false;
        }
        //动态计算列数
        $data_count = count($date_list);
        $cell_number = $data_count+3;
        $max_cell = $this->countCell($cell_number);
        $params['max_cell'] = $max_cell;
        $params['A1_text'] = $A1_text;
        $params['title'] = $title;

        // 调用导出方法
        $res = $this->export($data, $file_path, $params);
        if($res){
            $qiniu_res = $this->uploadQiniu($file_name,$file_path);
            return $qiniu_res;
        }
        return false;
    }
    /**
     * 上传七牛
     * [uploadQiniu description]
     * @param  [type] $file_name [description]
     * @param  [type] $file_path [description]
     * @return [type]            [description]
     */
    public function uploadQiniu($file_name,$file_path)
    {
        $domain = 'https://7img.512xhq.com/';
        $file_name = 'supaiban/excel/'.$file_name;
        $container = ApplicationContext::getContainer();
        $factory = $container->get(FilesystemFactory::class);
        $qiniu = $factory->get('qiniu');
        $qiniu->write($file_name, file_get_contents($file_path));
        return $domain.$file_name;
    }
    /**
     * 导出
     * [export description]
     * @param  [type] $data     [description]
     * @param  [type] $fileName [description]
     * @param  [type] $params   [description]
     * @return [type]           [description]
     */
    public function export($data, $file_name, $params)
    {
        // 创建Excel对象
        $spreadsheet = new Spreadsheet();
        // 设置工作表名称
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->setTitle($params['title'] ?: 'Sheet1');
        //设置全局列宽
        foreach ($params['max_cell'] as $cw) {
            if($cw=="A"){
                $worksheet->getColumnDimension($cw)->setWidth(20);
            }elseif($cw=="B"){
                $worksheet->getColumnDimension($cw)->setWidth(20);
            }else{
                $worksheet->getColumnDimension($cw)->setWidth(15);
            }
        }
        //设置全局垂直居中
        $spreadsheet->getDefaultStyle()
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_CENTER);
        //列-最后一个字母
        $max_cell_word = $params['max_cell'][count($params['max_cell'])-1];
        //行-数据数量
        $max_data_number = count($data);
        //第一行-标题
        $A1_to_cell = $max_cell_word."1";
        $worksheet->mergeCells('A1:'.$A1_to_cell);
        $worksheet->setCellValue('A1', $params['A1_text']);
        // 设置字体样式
        $worksheet->getStyle('A1')
            ->getFont()
            ->setBold(true) // 加粗
            ->setSize(14); // 设置大小
        $worksheet->getRowDimension(1)->setRowHeight(30);
        //中间-组别
        $worksheet->setCellValue('A2', "组别");
        $worksheet->setCellValue('B2', "姓名");
        $worksheet->getRowDimension(2)->setRowHeight(30);
        $worksheet->getCell('A2')
            ->getStyle('A2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $worksheet->getCell('B2')
            ->getStyle('B2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        //中间-未分组
        $worksheet->mergeCells('A3:A'.($max_data_number+2));
        $worksheet->setCellValue('A3', "未分组");
        $worksheet->getRowDimension(3)->setRowHeight(30);
        $worksheet->getCell('A3')
            ->getStyle('A3')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        //中间-数据体-日期
        $date_cell_start = 2;
        $week_ch = ['日','一','二','三','四','五','六'];
        foreach ($data[0]['list'] as $dlk => $dlv) {
            $wdate_text = $dlv['wdate'];
            $wdate_text .= "\n".$week_ch[date("w",strtotime($wdate_text))];
            $worksheet->setCellValue($params['max_cell'][$date_cell_start]."2", $wdate_text);
            $worksheet->getCell($params['max_cell'][$date_cell_start]."2")
                ->getStyle($params['max_cell'][$date_cell_start]."2")
                ->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                ->setWrapText(true);
            $date_cell_start++;
        }
        //中间-总工时
        $worksheet->setCellValue($max_cell_word.'2', "总工时");
        $worksheet->getCell($max_cell_word.'2')
            ->getStyle($max_cell_word.'2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $worksheet->getCell($max_cell_word.'2')
            ->getStyle($max_cell_word.'2')
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        //中间-数据体-姓名+排班
        foreach ($data as $uk => $user) {
            $hang = $uk+3;
            $worksheet->setCellValue('B'.$hang, $user['user_name']);
            $worksheet->getRowDimension($hang)->setRowHeight(30);
            $worksheet->getCell('B'.$hang)
                ->getStyle('B'.$hang)
                ->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $total_work_time = 0;
            foreach ($user['list'] as $lk => $lv) {
                if(!empty($lv['list'])){
                    if(!empty($lv['list'])){
                        $trad_name = new RichText();
                        foreach ($lv['list'] as $llk => $llv) {
                            $rcolor = str_replace("#", "", $llv['color_code']);
                            if($llk>0){
                                $trad_name->createTextRun("、");
                            }
                            $trad_name->createTextRun($llv['trad_name'])
                                ->getFont()
                                ->setColor(new Color($rcolor));
                            $total_work_time += $llv['work_hours'];
                        }
                    }else{
                        $trad_name = "-";
                    }
                }else{
                    $trad_name = "-";
                }
                $mc_key = $lk+2;
                $mc_cell_key = $params['max_cell'][$mc_key].$hang;
                $worksheet->setCellValue($mc_cell_key, $trad_name);
                $worksheet->getCell($mc_cell_key)
                    ->getStyle($mc_cell_key)
                    ->getAlignment()
                    ->setHorizontal(Alignment::HORIZONTAL_CENTER);
            }
            $worksheet->setCellValue($max_cell_word.$hang, $total_work_time."小时");
            $worksheet->getCell($max_cell_word.$hang)
                ->getStyle($max_cell_word.$hang)
                ->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }
        //空白行
        $Aspace_to_cell = $max_cell_word.($max_data_number+3);
        $worksheet->mergeCells('A'.($max_data_number+3).':'.$Aspace_to_cell);
        $worksheet->setCellValue('A'.($max_data_number+3), "");
        $worksheet->getRowDimension($max_data_number+3)->setRowHeight(30);
        //排班备注-头
        $Aremark_to_cell = $max_cell_word.($max_data_number+4);
        $worksheet->mergeCells('A'.($max_data_number+4).':'.$Aremark_to_cell);
        $worksheet->setCellValue('A'.($max_data_number+4), "排班备注");
        $worksheet->getRowDimension($max_data_number+4)->setRowHeight(30);
        $worksheet->getStyle('A'.($max_data_number+4))->getFont()
            ->setBold(true) // 加粗
            ->setSize(14); // 设置大小
        //排班备注-内容
        $Aremarkc_to_cell = $max_cell_word.($max_data_number+5);
        $worksheet->mergeCells('A'.($max_data_number+5).':'.$Aremarkc_to_cell);
        $Aremarkc_text = "";
        if(empty($params['trad_remark'])){
            $Aremarkc_text = "无";
        }else{
            $Aremarkc_text = $params['trad_remark'];
        }
        $worksheet->setCellValue('A'.($max_data_number+5), $Aremarkc_text);
        $worksheet->getRowDimension($max_data_number+5)->setRowHeight(80);
        $worksheet->getCell('A'.($max_data_number+5))
            ->getStyle('A'.($max_data_number+5))
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_TOP)
            ->setWrapText(true);
        //班种时间-头
        $Atrad_to_cell = $max_cell_word.($max_data_number+6);
        $worksheet->mergeCells('A'.($max_data_number+6).':'.$Atrad_to_cell);
        $worksheet->setCellValue('A'.($max_data_number+6), "班种时间");
        $worksheet->getRowDimension($max_data_number+6)->setRowHeight(30);
        $worksheet->getStyle('A'.($max_data_number+6))->getFont()
            ->setBold(true) // 加粗
            ->setSize(14); // 设置大小
        //班种时间-内容
        $Atradc_to_cell = $max_cell_word.($max_data_number+7);
        $worksheet->mergeCells('A'.($max_data_number+7).':'.$Atradc_to_cell);
        $Atradc_text = new RichText();
        if(!empty($params['trad_info'])){
            foreach ($params['trad_info'] as $tk => $tv) {
                $Atradc_cup = "";
                $tcolor = str_replace("#", "", $tv['color_code']);
                $work_time = [];
                if(!empty($tv['work_time'])){
                    foreach ($tv['work_time'] as $twtk => $twtv) {
                        $work_time[] = $twtv[0]."-".$twtv[1];
                    }
                }
                $work_time = implode("   ", $work_time);
                $Atradc_cup .= $tv['name'].":".$work_time."       ";
                $Atradc_text->createTextRun($Atradc_cup)
                    ->getFont()
                    ->setColor(new Color($tcolor));
            }
        }else{
            $Atradc_text = "";
        }
        $worksheet->setCellValue('A'.($max_data_number+7), $Atradc_text);
        $worksheet->getRowDimension($max_data_number+7)->setRowHeight(30);
        // 定义要设置边框的单元格范围
        $cellRange = 'A2:'.$Atradc_to_cell;
        $worksheet->getStyle($cellRange)
            ->getBorders()
            ->getAllBorders()
            ->setBorderStyle(Border::BORDER_THIN);

        // 导出Excel文件
        $writer = new Xlsx($spreadsheet);
        $writer->save($file_name);
        return true;
    }
    /**
     * 计算列
     * [countCell description]
     * @param  [type] $number [description]
     * @return [type]         [description]
     */
    public function countCell($number)
    {
        $base = [
            'A','B','C','D','E','F','G',
            'H','I','J','K','L','M','N',
            'O','P','Q','R','S','T','U',
            'V','W','X','Y','Z'
        ];
        $base_cup = $base;
        $max_turn = 6;
        $current_turn = 0;
        foreach ($base as $ok => $ov) {
            if($current_turn>=$max_turn){
                break;
            }
            foreach ($base as $ik => $iv) {
                $base_cup[] = $base[$current_turn].$iv;
            }
            $current_turn++;
        }
        $return_data = [];
        for ($i=0; $i<$number; $i++) { 
            $return_data[] = $base_cup[$i];
        }
        return $return_data;
    }
}
